package com.yss.common.core.utils;


import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.util.JdbcConstants;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.StringJoiner;

public class ExportSqlUtil {


/*    public static void initConnection(String driver, String url, String userName, String password) {
        try {
            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(url, userName, password);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }*/


    /***
     *   根据查询SQL构建插入脚本
     * @param tableName  表名
     * @param condition 查询条件  eg: where id =1
     * @return java.util.List<java.lang.String>
     */
    public static List<String> executeSQLList(String tableName, String condition, Connection conn) throws Exception {
        ResultSet rs = null;
        PreparedStatement pStemt = null;
        List<String> insertList = new ArrayList<>();
        //建立查询语句
        String querySql = "SELECT * FROM " + tableName + condition;
        try {
            //执行查询并拼装insert语句
            pStemt = conn.prepareStatement(querySql);
            rs = pStemt.executeQuery();
            //获取表元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //获取当前表列的长度
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                StringJoiner columnName = new StringJoiner(",");
                StringJoiner columnValue = new StringJoiner(",");
                for (int i = 1; i <= columnCount; i++) {
                    String value = rs.getString(i);
                    //获取当前列数据
                    columnName.add(rsmd.getColumnName(i));
                    if (StringUtils.isEmpty(value)) {
                        columnValue.add(value);
                        continue;
                    }
                    if (value.indexOf("'") > -1) {
                        value = value.replaceAll("\\'", "\\'\\'");
                    }
                    //字段列名称
                    if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i)
                            || Types.LONGVARCHAR == rsmd.getColumnType(i) || Types.NVARCHAR == rsmd.getColumnType(i)
                            || Types.CLOB == rsmd.getColumnType(i) || Types.BLOB == rsmd.getColumnType(i)
                    ) {
                        columnValue.add("'" + value + "'");
                        continue;
                    }
                    if (Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i)
                            || Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i)
                            || Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i)
                            || Types.DECIMAL == rsmd.getColumnType(i) || Types.TINYINT == rsmd.getColumnType(i)
                    ) {
                        columnValue.add(value);
                        continue;
                    }
                    if (Types.DATE == rsmd.getColumnType(i)) {
                        columnValue.add("TO_DATE(" + value + ",'" + DateUtils.YYYY_MM_DD + "')");
                        continue;
                    }
                    if (Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {
                        columnValue.add("TIMESTAMP'" + value + "'");
                        continue;
                    }
                    columnValue.add(value);
                }
                StringBuffer insertSql = new StringBuffer();
                insertSql.append("INSERT INTO ")/*.append(schema).append(".")*/
                        .append(tableName).append("(").append(columnName.toString()).append(")")
                        .append("VALUES").append("(").append(columnValue.toString()).append(");");
                insertList.add(insertSql.toString());
            }
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        } finally {
            //关闭数据库连接
            try {
                JdbcUtil.close(rs, pStemt, conn);
            } catch (Exception ex) {
                throw new Exception(ex.getMessage());
            }
        }
        return insertList;
    }


    /***
     *   根据查询SQL构建插入脚本
     * @param tableName  表名
     * @param condition 查询条件  eg: where id =1
     * @return java.util.List<java.lang.String>
     */
    public static String executeSQLString(String tableName, String condition, Connection conn) throws Exception {
        ResultSet rs = null;
        PreparedStatement pStemt = null;
        StringBuffer insertSql = new StringBuffer();
        try {
            //建立查询语句
            String querySql = "SELECT * FROM " + tableName + " " + condition;
            //执行查询并拼装insert语句
            pStemt = conn.prepareStatement(querySql);
            rs = pStemt.executeQuery();
            //获取表元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //获取当前表列的长度
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                StringJoiner columnName = new StringJoiner(",");
                StringJoiner columnValue = new StringJoiner(",");
                for (int i = 1; i <= columnCount; i++) {
                    String value = rs.getString(i);
                    //获取当前列数据
                    columnName.add(rsmd.getColumnName(i));
                    if (StringUtils.isEmpty(value)) {
                        columnValue.add(value);
                        continue;
                    }
                    if (value.indexOf("'") > -1) {
                        value = value.replaceAll("\\'", "\\'\\'");
                    }
                    //字段列名称
                    if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i)
                            || Types.LONGVARCHAR == rsmd.getColumnType(i) || Types.NVARCHAR == rsmd.getColumnType(i)
                            || Types.CLOB == rsmd.getColumnType(i) || Types.BLOB == rsmd.getColumnType(i)
                    ) {
                        columnValue.add("'" + value + "'");
                        continue;
                    }
                    if (Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i)
                            || Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i)
                            || Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i)
                            || Types.DECIMAL == rsmd.getColumnType(i) || Types.TINYINT == rsmd.getColumnType(i)
                    ) {
                        columnValue.add(value);
                        continue;
                    }
                    if (Types.DATE == rsmd.getColumnType(i)) {
                        columnValue.add("TO_DATE(" + value + ",'" + DateUtils.YYYY_MM_DD + "')");
                        continue;
                    }
                    if (Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {
                        columnValue.add("TIMESTAMP'" + value + "'");
                        continue;
                    }
                    columnValue.add(value);
                }
                insertSql.append("INSERT INTO ")/*.append(schema).append(".")*/
                        .append(tableName).append("(").append(columnName.toString()).append(")")
                        .append("VALUES").append("(").append(columnValue.toString()).append(");").append("\r\n");
            }
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        } finally {
            //关闭数据库连接
            try {
                JdbcUtil.close(rs);
                JdbcUtil.close(pStemt);
            } catch (Exception ex) {
                throw new Exception(ex.getMessage());
            }
        }
        return insertSql.toString();
    }

    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.OracleDriver").newInstance();
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "bbsj_stander", "bbsj_stander");
            executeSQLString("ETL_JOB_DETAIL", " ", conn);
        } catch (Exception ex) {

        }

    }
}
